import json
import re

import pandas as pd
import requests
from django.conf import settings

from django.db import models
from django.db.models.signals import pre_save
from django.dispatch import receiver
from django.utils.translation import gettext_lazy as _

from analysis.auto_get_storage import get_zhsm_token, get_kingdee_token
from oa.models import Customer
from tb3.utils import str_value, num_value, date_value


sales_channel_choices = [(1, '上海'), (2, '杭州')]


class KingdeeProductInfo(models.Model):
    product_code = models.CharField(_('商品编号'), max_length=255, null=True, unique=True)
    product_name = models.CharField(_('商品名称'), max_length=255, null=True, blank=True)
    package = models.CharField(_('规格型号'), max_length=255, null=True, blank=True)
    unit = models.CharField(_('单位'), max_length=255, null=True, blank=True)

    objects = models.Manager()

    def __str__(self):
        return "{0}-{1}-{2}-{3}".format(
            self.product_code, self.product_name, self.package, self.unit
        )

    class Meta:
        ordering = ['id']
        verbose_name = _('金蝶商品信息')
        verbose_name_plural = _('金蝶商品信息')


class StorageProductInfo(models.Model):
    package_group = models.ForeignKey(
        'oa.PackageGroup',
        null=True,
        blank=True,
        on_delete=models.SET_NULL,
        verbose_name=_('包装组合'),
        related_name='analysis_package_group'
    )
    product_name = models.CharField(_('商品名称'), max_length=255, null=True, blank=True)
    package = models.CharField(_('规格型号'), max_length=255, null=True, blank=True)
    unit = models.CharField(_('单位'), max_length=255, null=True, blank=True)
    is_show = models.BooleanField(_('显示'), default=True)
    is_linked = models.BooleanField(_('已关联'), default=False)
    need_statistic = models.BooleanField(_('需要统计'), default=True)
    datetime_created = models.DateTimeField(_('创建时间'), auto_now_add=True)
    datetime_updated = models.DateTimeField(_('更新时间'), auto_now=True)

    objects = models.Manager()

    def __str__(self):
        return "{0}-{1}-{2}-{3}".format(
            self.product_name, self.package, self.unit, self.package_group
        )

    class Meta:
        ordering = ['-id']
        verbose_name = _('产品对应信息')
        verbose_name_plural = _('产品对应信息')


class SalesRecordUpload(models.Model):
    file = models.FileField(_('文件'), null=True, blank=True, upload_to='sales/%Y/%m/%d/')
    file_name = models.CharField(_('文件名称'), null=True, blank=True, max_length=255)
    total_count = models.IntegerField(_('总数'), null=True, blank=True)
    created_count = models.IntegerField(_('导入成功数'), null=True, blank=True)
    updated_count = models.IntegerField(_('更新条目数'), null=True, blank=True)
    failed_count = models.IntegerField(_('导入失败数'), null=True, blank=True)
    is_imported = models.BooleanField(_('已经导入'), default=False)
    created_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        verbose_name=_('创建人'),
        related_name='analysis_sales_record_upload_created_by',
    )
    updated_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        verbose_name=_('更新人'),
        related_name='analysis_sales_record_upload_updated_by',
    )
    datetime_created = models.DateTimeField(_('创建时间'), auto_now_add=True)
    datetime_updated = models.DateTimeField(_('更新时间'), auto_now=True)

    objects = models.Manager()

    class Meta:
        ordering = ['-id']
        verbose_name = _('订单导入')
        verbose_name_plural = _('订单导入')

    def __str__(self):
        return "{0} {1}".format(
            self.file, self.total_count
        )

    def import_data_from_excel(self):
        """
        数据导入模块
        """
        if self.file:
            df = pd.read_excel(getattr(self, 'file').path, 0, keep_default_na=False)
            self.is_imported = True
            sales_channel = None
            if df.shape[1] > 22:
                sales_channel = 1  # 上海
                excel_conf = {
                    'fields': {
                        'product_name': '商品名称',
                        'sales_date': '业务日期',
                        'sales_order': '单据编号',
                        'customer': '客户名称',
                        'product_code': '商品编号',
                        'product_model': '规格',
                        'unit': '单位',
                        'warehouse': '仓库',
                        'amount': '数量',
                        'unit_price': '单价',
                        'income': '金额',
                        'notes': '备注',
                    },
                    'str_fields': [
                        'product_name', 'sales_order', 'customer', 'product_code', 'product_model',
                        'unit', 'warehouse', 'notes'],
                    'num_fields_2': ['amount', 'unit_price', 'income'],
                    'date_fields': ['sales_date']
                }
            elif 17 <= df.shape[1] <= 22:
                sales_channel = 2  # 杭州
                df = pd.read_excel(getattr(self, 'file').path, 0, skiprows=4, keep_default_na=False)
                excel_conf = {
                    'fields': {
                        'sales_date': '销售日期',
                        'sales_order': '销售单据号',
                        'salesman': '销售人员',
                        'customer': '客户名称',
                        'product_name': '商品名称',
                        'product_code': '商品编号',
                        'product_model': '规格型号',
                        'unit': '单位',
                        'warehouse': '仓库',
                        'amount': '数量',
                        'unit_price': '单价',
                        'income': '销售收入',
                        'notes': '整单备注',
                    },
                    'str_fields': [
                        'product_name', 'sales_order', 'customer', 'product_code', 'product_model', 'salesman',
                        'unit', 'warehouse', 'notes'],
                    'num_fields_2': ['amount', 'unit_price', 'income'],
                    'date_fields': ['sales_date']
                }
            else:
                excel_conf = None
            if excel_conf:
                fields = excel_conf['fields']
                str_fields = excel_conf['str_fields']
                num_fields_2 = excel_conf['num_fields_2']
                date_fields = excel_conf['date_fields']
                self.total_count = 0
                self.created_count = 0
                self.updated_count = 0
                self.failed_count = 0
                for r in df.itertuples():
                    data = {}
                    for k, v in fields.items():
                        try:
                            vl = getattr(r, v)
                            if k in str_fields:
                                data[k] = str_value(vl)
                            elif k in num_fields_2:
                                data[k] = num_value(vl, 2)
                            elif k in date_fields:
                                data[k] = date_value(vl)
                            else:
                                data[k] = vl
                        except AttributeError:
                            pass
                    if data['sales_date']:
                        self.total_count += 1
                        product_name = data['product_name']
                        if product_name:
                            if '_' in product_name:
                                product_name = re.sub(r'_\d{8}/\d{8}', '', product_name)
                                product_name = product_name.strip()
                        related_product_info, created = StorageProductInfo.objects.get_or_create(
                            product_name=product_name,
                            package=data['product_model'],
                            unit=data['unit']
                        )
                        related_customer, created = Customer.objects.get_or_create(
                            nickname=data['customer']
                        )
                        SalesRecord.objects.create(
                            related_product_info=related_product_info,
                            related_upload_record_id=self.pk,
                            related_customer=related_customer,
                            sales_channel=sales_channel, **data
                        )
                        self.created_count += 1
                self.failed_count = self.total_count - self.created_count

    def save(self, **kwargs):
        if not self.file_name:
            self.file_name = getattr(self, 'file').name
        super(SalesRecordUpload, self).save(**kwargs)
        if self.file and self.is_imported is False:
            self.import_data_from_excel()
            super(SalesRecordUpload, self).save(
                update_fields=['is_imported', 'total_count', 'created_count', 'updated_count', 'failed_count']
            )


class Commission(models.Model):
    related_staff = models.ManyToManyField(
        'oa.Staff',
        blank=True,
        verbose_name=_('关联员工')
    )
    issue_date = models.DateField(_('发放日期'), null=True)
    is_issued = models.BooleanField(_('已发放'), default=False)
    notes = models.TextField(_('备注'), max_length=1000, null=True, blank=True)

    objects = models.Manager()

    class Meta:
        ordering = ['-id']
        verbose_name = _('销售提成记录')
        verbose_name_plural = _('销售提成记录')

    def __str__(self):
        return "{0} {1}".format(
            self.pk, self.issue_date
        )


class SalesRecord(models.Model):
    sales_date = models.DateField(_('销售日期'), null=True, blank=True)
    sales_order = models.CharField(_('销售单据号'), max_length=255, null=True, blank=True)
    sales_type = models.CharField(_('业务类别'), max_length=255, null=True, blank=True)
    salesman = models.CharField(_('销售人员'), max_length=255, null=True, blank=True)
    customer = models.CharField(_('客户'), max_length=255, null=True, blank=True)
    product_type = models.CharField(_('商品类别'), max_length=255, null=True, blank=True)
    product_code = models.CharField(_('商品编号'), max_length=255, null=True, blank=True)
    product_name = models.CharField(_('商品名称'), max_length=255, null=True, blank=True)
    product_model = models.CharField(_('规格型号'), max_length=255, null=True, blank=True)
    unit = models.CharField(_('单位'), max_length=255, null=True, blank=True)
    warehouse = models.CharField(_('仓库'), max_length=255, null=True, blank=True)
    #
    amount = models.IntegerField(_('数量'), null=True, blank=True)
    unit_price = models.DecimalField(_('单价'), max_digits=10, decimal_places=2, null=True, blank=True)
    income = models.DecimalField(_('销售收入'), max_digits=10, decimal_places=2, null=True, blank=True)
    cost = models.DecimalField(_('生产成本'), max_digits=10, decimal_places=2, null=True, blank=True)
    sales_cost = models.DecimalField(
        _('出厂成本'), max_digits=10, decimal_places=2, null=True, blank=True)  # 部门结算价格
    has_cost = models.BooleanField(_('有生产成本'), default=False)
    has_sales_cost = models.BooleanField(_('有出厂成本'), default=False)
    # notes
    notes = models.CharField(_('备注'), max_length=255, null=True, blank=True)
    related_product_info = models.ForeignKey(
        StorageProductInfo,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        verbose_name=_('产品信息')
    )
    related_customer = models.ForeignKey(
        'oa.Customer',
        on_delete=models.SET_NULL,
        null=True, blank=True,
        verbose_name=_('关联客户'),
        related_name='analysis_related_customer'
    )
    related_upload_record = models.ForeignKey(
        SalesRecordUpload,
        on_delete=models.CASCADE,
        null=True, blank=True,
        verbose_name=_('关联上传记录')
    )
    related_commission = models.ForeignKey(
        Commission,
        on_delete=models.SET_NULL,
        null=True, blank=True,
        verbose_name=_('关联销售提成')
    )
    sales_channel = models.SmallIntegerField(_('销售渠道'), null=True, blank=True, choices=sales_channel_choices)
    commission_payed = models.BooleanField(_('提成已发放'), default=False)
    #
    datetime_created = models.DateTimeField(_('创建时间'), auto_now_add=True)
    datetime_updated = models.DateTimeField(_('更新时间'), auto_now=True)

    objects = models.Manager()

    class Meta:
        ordering = ['-id']
        verbose_name = _('销售记录')
        verbose_name_plural = _('销售记录')

    def __str__(self):
        return "{0} {1}".format(
            self.pk, self.sales_order
        )


def get_zhsm_inventory(related_calculate_id=1):
    url = settings.ZHSM_INVENTORY_URL
    headers = {
        'appid': '5',
        'productversion': '1',
        'transno': '083cdfde-6845-3191-3764-b7d9560d06c5',
        'content-type': 'application/json;charset=UTF-8',
        'timestamp': '1598860192',
        'sign': 'e696484649d2e140f0b2363eb3ad0382',
        'token': get_zhsm_token()
    }
    js = {
        "Page": '1',
        "Rp": '100',
        "QueryPageDataOnly": "0",
        "QueryType": "0",
        "SearchKey": "",
        "ClassId": "",
        "WarehouseId": "",
        "StartDate": "",
        "EndDate": "",
        "UserId": "68ca44ff-a1e2-487a-bed9-fee6a93f9610",
        "OpenSOBDate": "null",
        "IsShowZero": "1",
        "IsShowStop": "",
        "BranchId": "7126db53-5d79-4d2a-a918-160fae3a9f3a",
        "LoginClientIP": "115.196.191.102",
        "LoginClientIPAddress": "浙江省杭州市",
        "LoginClientType": 1
    }
    res = requests.post(url=url, headers=headers, json=js)
    data = json.loads(res.text).get('Data')
    products = data.get('ProductList')
    fields = {
        'product_category': 'classname',
        'product_code': 'productcode',
        'product_name': 'productname',
        'package': 'productform',
        'unit': 'mainunitname',
        'current_storage': 'availablestock',
        'barcode': 'barcode',
    }
    order_index = 0
    for p in products:
        order_index += 1
        r_d = dict()
        for k, v in fields.items():
            r_d[k] = p.get(v)
        r_d['warehouse'] = '上海'
        related_product_info, created = StorageProductInfo.objects.get_or_create(
            product_name=r_d['product_name'],
            package=r_d['package'],
            unit=r_d['unit']
        )
        r_d['related_product_info'] = related_product_info
        Storage.objects.update_or_create(
            related_calculate_id=related_calculate_id,
            order_index=order_index,
            file_index=2,
            defaults=r_d
        )


def get_kingdee_product_info(product_code):
    url = settings.KINGDEE_PRODUCT_URL
    dbid = settings.KINGDEE_DB_ID
    access_token = get_kingdee_token()
    url = '{}?access_token={}&dbId={}'.format(url, access_token, dbid)
    request_json = {'filter': {'productNumber': product_code}}
    res = requests.post(url=url, json=request_json)
    res_json = res.json()
    items = res_json.get('items')
    if items:
        for item in items:
            data = {
                'product_name': item.get('productName'),
                'package': item.get('spec'),
                'unit': item.get('unitName'),
            }
            KingdeeProductInfo.objects.update_or_create(
                defaults=data,
                product_code=product_code
            )
            return data
    return None


def get_kingdee_inventory(related_calculate_id=1):
    page = 1
    url = settings.KINGDEE_INVENTORY_URL
    dbid = settings.KINGDEE_DB_ID
    access_token = get_kingdee_token()
    product_list = list()
    rf_info = {
        'product_code': 'productNumber',
        'product_name': 'productName',
        'current_storage': 'qty',
        'warehouse': 'locationName'
    }
    kingdee_product_infos = KingdeeProductInfo.objects.all().values(
        'product_code', 'product_name', 'package', 'unit'
    )
    kingdee_product_info_dict = dict()
    for kingdee_product_info in kingdee_product_infos:
        kingdee_product_info_dict[kingdee_product_info['product_code']] = kingdee_product_info
    order_index = 0
    while True:
        params = {
            'access_token': access_token,
            'dbId': dbid,
            'pageSize': 500,
            'page': page,
            'status': 0
        }
        res = requests.get(url=url, params=params)
        res_json = res.json()
        total_pages = res_json.get('totalPages')
        items = res_json.get('items')
        for item in items:
            row_data = dict()
            for k, v in rf_info.items():
                row_data[k] = item[v]
            product_info = kingdee_product_info_dict.get(row_data.get('product_code'))
            if not product_info:
                product_info = get_kingdee_product_info(row_data.get('product_code'))
            if product_info:
                row_data['package'] = product_info.get('package')
                row_data['unit'] = product_info.get('unit')
            product_list.append(row_data)
            related_product_info, created = StorageProductInfo.objects.get_or_create(
                product_name=row_data['product_name'],
                package=row_data.get('package'),
                unit=row_data.get('unit')
            )
            row_data['related_product_info'] = related_product_info
            Storage.objects.update_or_create(
                related_calculate_id=related_calculate_id,
                order_index=order_index,
                file_index=1,
                defaults=row_data
            )
            order_index += 1
        page += 1
        if page > total_pages:
            break


class StorageCalculate(models.Model):
    recorded_at = models.DateField(_('统计日期'), null=True)
    hz_file = models.FileField(_('杭州数据'), null=True, blank=True, upload_to='storage')
    sh_file = models.FileField(_('上海数据'), null=True, blank=True, upload_to='storage')
    is_imported = models.BooleanField(_('已经导入'), default=False)
    datetime_created = models.DateTimeField(_('创建时间'), auto_now_add=True)
    datetime_updated = models.DateTimeField(_('更新时间'), auto_now=True)

    objects = models.Manager()

    def import_data_from_excel(self):
        self.is_imported = True
        if self.hz_file:
            df_1 = pd.read_excel(getattr(self, 'hz_file').path, skiprows=4, keep_default_na=False)
            fields = {
                'warehouse': '仓库',
                'product_category': '商品类别',
                'product_code': '商品编号',
                'product_name': '商品名称',
                'property_code': '属性编号',
                'property_name': '属性名称',
                'package': '规格型号',
                'unit': '单位',
                'current_storage': '系统库存',
                'real_storage': '盘点库存',
                'barcode': '商品条码',
            }
            str_fields = [
                'warehouse', 'product_category', 'product_code', 'product_name',
                'property_code', 'property_name', 'package', 'unit', 'barcode']
            int_fields = ['current_storage']
            order_index_1 = 0
            for r in df_1.itertuples():
                order_index_1 += 1
                data = {}
                for k, v in fields.items():
                    try:
                        vl = getattr(r, v)
                        if k in str_fields:
                            data[k] = str_value(vl)
                        elif k in int_fields:
                            data[k] = num_value(vl, 0)
                        else:
                            data[k] = vl
                    except AttributeError:
                        pass
                related_product_info, created = StorageProductInfo.objects.get_or_create(
                    product_name=data['product_name'],
                    package=data['package'],
                    unit=data['unit']
                )
                data['related_product_info'] = related_product_info
                Storage.objects.update_or_create(
                    related_calculate_id=self.pk,
                    order_index=order_index_1,
                    file_index=1,
                    defaults=data
                )
        else:
            get_kingdee_inventory(self.pk)
        if self.sh_file:
            df_2 = pd.read_excel(getattr(self, 'sh_file').path, keep_default_na=False)
            fields = {
                'product_category': '商品分类',
                'product_code': '商品编号',
                'product_name': '商品名称',
                'package': '规格',
                'unit': '单位',
                'current_storage': '可用库存',
                'current_left': '本期结余',
                'barcode': '条形码',
            }
            str_fields = [
                'product_category', 'product_code', 'product_name', 'package', 'unit', 'barcode']
            int_fields = ['current_storage', 'current_left']
            order_index_2 = 0
            for r in df_2.itertuples():
                order_index_2 += 1
                data = {}
                for k, v in fields.items():
                    try:
                        vl = getattr(r, v)
                        if k in str_fields:
                            data[k] = str_value(vl)
                        elif k in int_fields:
                            data[k] = num_value(vl, 0)
                        else:
                            data[k] = vl
                    except AttributeError:
                        pass
                data['warehouse'] = '上海'
                related_product_info, created = StorageProductInfo.objects.get_or_create(
                    product_name=data['product_name'],
                    package=data['package'],
                    unit=data['unit']
                )
                data['related_product_info'] = related_product_info
                if not data.get('current_storage'):
                    data['current_storage'] = data['current_left']
                data.pop('current_left')
                Storage.objects.update_or_create(
                    related_calculate_id=self.pk,
                    order_index=order_index_2,
                    file_index=2,
                    defaults=data
                )
        else:
            get_zhsm_inventory(self.pk)

    def save(self, **kwargs):
        super(StorageCalculate, self).save(**kwargs)
        if self.is_imported is False:
            self.import_data_from_excel()
            self.is_imported = True
            super(StorageCalculate, self).save(
                update_fields=['is_imported']
            )

    def __str__(self):
        return "{0}-{1}".format(
            self.pk, self.recorded_at
        )

    class Meta:
        ordering = ['-id']
        verbose_name = _('库存明细导入')
        verbose_name_plural = _('库存明细导入')


class Storage(models.Model):
    related_calculate = models.ForeignKey(
        StorageCalculate,
        on_delete=models.CASCADE,
        null=True,
        verbose_name=_('导入记录')
    )
    related_product_info = models.ForeignKey(
        StorageProductInfo,
        on_delete=models.SET_NULL,
        null=True,
        verbose_name=_('产品信息')
    )
    order_index = models.IntegerField(_('序列号'), null=True, blank=True)
    file_index = models.IntegerField(_('文件序列号'), null=True, blank=True)
    warehouse = models.CharField(_('仓库'), max_length=255, null=True, blank=True)
    product_category = models.CharField(_('商品类别'), max_length=255, null=True, blank=True)
    product_code = models.CharField(_('商品编号'), max_length=255, null=True, blank=True)
    product_name = models.CharField(_('商品名称'), max_length=255, null=True, blank=True)
    property_code = models.CharField(_('属性编号'), max_length=255, null=True, blank=True)
    property_name = models.CharField(_('属性名称'), max_length=255, null=True, blank=True)
    package = models.CharField(_('规格型号'), max_length=255, null=True, blank=True)
    unit = models.CharField(_('单位'), max_length=255, null=True, blank=True)
    current_storage = models.BigIntegerField(_('系统库存'), null=True, blank=True)
    real_storage = models.CharField(_('盘点库存'), max_length=255, null=True, blank=True)
    barcode = models.CharField(_('商品条码'), max_length=255, null=True, blank=True)
    datetime_created = models.DateTimeField(_('创建时间'), auto_now_add=True)
    datetime_updated = models.DateTimeField(_('更新时间'), auto_now=True)

    objects = models.Manager()

    def __str__(self):
        return "{0}-{1}-{2}-{3}".format(
            self.file_index,
            self.order_index,
            self.product_name,
            self.current_storage
        )

    class Meta:
        ordering = ['-id']
        verbose_name = _('库存明细')
        verbose_name_plural = _('库存明细')


@receiver(pre_save, sender=StorageProductInfo)
def pre_save_storage_product_info(sender, instance, **kwargs):
    if instance.package_group:
        instance.is_linked = True
    else:
        instance.is_linked = False


@receiver(pre_save, sender=SalesRecord)
def pre_save_sales_record(sender, instance, **kwargs):
    if instance.related_product_info:
        if instance.related_product_info.package_group:
            reference_cost = instance.related_product_info.package_group.reference_cost
            sales_cost = instance.related_product_info.package_group.sales_cost
            # 生产成本，生产的实际成本
            if reference_cost and instance.amount:
                instance.cost = float(reference_cost) * int(instance.amount)
            if instance.cost:
                instance.has_cost = True
            else:
                instance.has_cost = False
            # 出厂成本，销售公司结算价
            if sales_cost and instance.amount:
                instance.sales_cost = float(sales_cost) * int(instance.amount)
            if instance.sales_cost:
                instance.has_sales_cost = True
            else:
                instance.has_sales_cost = False
